from math import floor

import MySQL
import Visualization
import Normalization
import Config as C


def get_max(tables, col, where_clause='null'):
    if not where_clause == 'null':
        where_clause = ' WHERE ' + where_clause
    else:
        where_clause = ''
    max_values = {}
    for table in tables:
        sql = 'SELECT MAX(' + col + ') AS max_value FROM ' + table + where_clause
        max_value = MySQL.query(sql)[0]['max_value']
        max_values[table] = max_value
    return max_values


def get_min(tables, col, where_clause='null'):
    if not where_clause == 'null':
        where_clause = ' WHERE ' + where_clause
    else:
        where_clause = ''
    min_values = {}
    for table in tables:
        sql = 'SELECT MIN(' + col + ') AS min_value FROM ' + table + where_clause
        min_value = MySQL.query(sql)[0]['min_value']
        min_values[table] = min_value
    return min_values


def find_max(max_values):
    max_value = -99999999
    for value in max_values.values():
        if max_value < value:
            max_value = value
    return max_value


def resolve(dist_id):
    sql = 'SELECT * FROM `tpch_distribution` WHERE `id`=' + str(dist_id)
    dist = MySQL.query(sql)[0]
    max_value = list(get_max([dist['table'] + '_' + str(C.DEFAULT_DATA_END) + 'w'],
                             dist['column'], dist['where_clause']).values())[0]
    min_value = list(get_min([dist['table'] + '_' + str(C.DEFAULT_DATA_END) + 'w'],
                             dist['column'], dist['where_clause']).values())[0]
    for i in range(C.DEFAULT_DATA_START, C.DEFAULT_DATA_END + C.DEFAULT_DATA_DELTA - 1, C.DEFAULT_DATA_DELTA):
        data_table = dist['table'] + '_' + str(i) + 'w'
        target_table = data_table + '_dist_' + dist['column_label'] + '_' + str(dist['unit_size']) + '_' + str(dist['id'])
        sql = 'DROP TABLE IF EXISTS ' + target_table
        MySQL.execute(sql)
        where_clause = ''
        if dist['where_clause'] != 'null':
            where_clause = ' WHERE ' + dist['where_clause']
        sql = 'CREATE TABLE {target_table}(`{group_label}` INT, `{count_label}` DOUBLE, `normalized` INT, `size` INT, ' \
              'PRIMARY KEY (`{group_label}`)) '.format(target_table=target_table, group_label=dist['group_label'],
                                                       count_label=dist['count_label'])
        MySQL.execute(sql)
        sql = 'SELECT FLOOR(' + dist['column'] + ' / ' + str(dist['unit_size']) + ') * ' + str(dist['unit_size']) + ' AS ' + dist['group_label'] + ', COUNT(*) AS ' + dist['count_label'] + ', ' + str(i) + ' AS size, 0 AS normalized FROM ' + data_table + where_clause + ' GROUP BY ' + dist['group_label']
        results = MySQL.query(sql)
        for j in range(floor(min_value / dist['unit_size']) * dist['unit_size'], floor(max_value / dist['unit_size']) * dist['unit_size'] + 1, dist['unit_size']):
            row = None
            for k in results:
                if k[dist['group_label']] == j:
                    row = k
                    break
            if row is None:
                row = {dist['group_label']: j, dist['count_label']: 0, 'normalized': 0, 'size': i}
            MySQL.save(target_table, row)

        # sql = 'CREATE TABLE ' + target_table + ' SELECT FLOOR(' + dist['column'] + ' / ' + str(dist['unit_size']) + ') * ' + str(dist['unit_size']) + ' AS ' + dist['group_label'] + ', COUNT(*) AS ' + dist['count_label'] + ', ' + str(i) + ' AS size, 0 AS normalized FROM ' + data_table + where_clause + ' GROUP BY ' + dist['group_label']
        # MySQL.execute(sql)
        # sql = 'ALTER TABLE ' + target_table + ' ADD PRIMARY KEY(' + dist['group_label'] + ')'
        # MySQL.execute(sql)


def clean(dist_id):
    sql = 'SELECT * FROM `tpch_distribution` WHERE `id`=' + str(dist_id)
    dist = MySQL.query(sql)[0]
    for i in range(C.DEFAULT_DATA_START, C.DEFAULT_DATA_END + C.DEFAULT_DATA_DELTA - 1, C.DEFAULT_DATA_DELTA):
        data_table = dist['table'] + '_' + str(i) + 'w'
        target_table = data_table + '_dist_' + dist['column_label'] + '_' + str(dist['unit_size']) + '_' + str(dist['id'])
        sql = 'DROP TABLE IF EXISTS ' + target_table
        MySQL.execute(sql)


def draw(dist_id):
    sql = 'SELECT * FROM `tpch_distribution` WHERE `id`=' + str(dist_id)
    dist = MySQL.query(sql)[0]
    group_label = dist['group_label']
    count_label = dist['count_label']
    maps = {}
    for i in range(C.DEFAULT_DATA_START, C.DEFAULT_DATA_END + C.DEFAULT_DATA_DELTA - 1, C.DEFAULT_DATA_DELTA):
        dist_table = dist['table'] + '_' + str(i) + 'w' + '_dist_' + dist['column_label'] + '_' + str(dist['unit_size']) + '_' + str(dist['id'])
        sql = 'SELECT * FROM ' + dist_table
        data_dist = MySQL.query(sql)
        xs = []
        ys = []
        for j in data_dist:
            x = round(j[group_label])
            y = j[count_label]
            xs.append(x)
            ys.append(y)
        maps[dist['table'] + '_' + str(i) + 'w'] = {'x': xs, 'y': ys}
    Visualization.draw(maps)


if __name__ == '__main__':
    # clean(6)
    k = 8
    # resolve(k)
    # Normalization.normalize_distribution(k)
    draw(k)




    # print(getMax(['lineitem_inc_500w'], 'l_extendedprice * l_discount'))
    # print(getMin(['lineitem_inc_500w'], 'l_extendedprice * l_discount'))